package junghoAndI;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;



public class DogDAO {
	private final String insertSQL = "insert into DOG(id, dname, sex, tno, age, regdate, collar, fno, specificloc, sno, etc, mercykilling, photo) values(id.nextval, ?, ?, ?, ?, sysdate, ?, ?, ?, ?, ?, ?, ?)";
	private final String updateSQL = "update DOG set regdate = sysdate, collar = ?, sno = ?, etc = ?, mercykilling = ? where id = ?";
	private final String deleteSQL = "delete from DOG where id=?";
	private final String selectSQL = "select d.id, d.dname, d.sex, t.type, d.age, d.fno, s.sheltername, d.mercykilling from DOG d, SHELTERS s, dogtype t where d.sno = s.sno and d.tno = t.tno";
	private final String findTnoSQL = "select tno from dogtype where type = ?";
	private final String findSnoSQL = "select sno from shelters where sheltername = ?";
	private final String selectOneSQL = "select * from DOG where id=?";
	private final String mercykillingSQL = "select d.id, d.dname, d.sex, t.tno, d.age, d.fno, s.sno, d.mercykilling from DOG d, SHELTERS s, dogtype t where mercykilling = '아니오' and d.sno = s.sno and d.tno = t.tno";
	private final String findShelternameSQL = "select sheltername from shelters where sno = ?";
	private final String findTypeSQL = "select type from dogtype where tno = ?";
	
	
	// 회원가입 insert
	public int insertDog(DogVO m){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, m.getDname());
				ps.setString(2, m.getSex());
				ps.setString(3, m.getTno());
				ps.setString(4, m.getAge());
				ps.setString(5, m.getCollar());
				ps.setString(6, m.getFno());
				ps.setString(7, m.getSpecificloc());
				ps.setString(8, m.getSno());
				ps.setString(9, m.getEtc());
				ps.setString(10, m.getMercykilling());
				ps.setString(11, m.getPhoto());
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	// 회원수정 update
	public int updateDog(DogVO m){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, m.getCollar());
				ps.setString(2, m.getSno());
				ps.setString(3, m.getEtc());
				ps.setString(4, m.getMercykilling());
				ps.setString(5, m.getID());
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
				
		return cnt;
	}
	
	// 회원삭제 delete
	public int deleteDog(String id){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, id);
			
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}

		return cnt;
	}
	
	// 회원전체조회 select
	public ArrayList<DogVO> selectDog(){
		ArrayList<DogVO> list = new ArrayList<>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			// select d.id, d.dname, d.sex, t.type, d.age, d.fno, s.sheltername, d.mercykilling from DOG d, SHELTERS s, dogtype t where d.sno = s.sno and d.tno = t.tno
			while(rs.next()){
				DogVO m = new DogVO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), null, null, rs.getString(6), null, rs.getString(7), null, rs.getString(8), null);
				
/*				DogVO m = new DogVO( rs.getString(1), rs.getString(2), rs.getString(3), null, rs.getString(4), null, null, rs.getString(5), null,
						rs.getString(6), null, rs.getString(7),  rs.getString(8) );
*/						
				//m.setDogno(rs.getString(1));
				//m.setRegdate(rs.getString(6));
				list.add(m);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		
		return list;
	}
	
	// 회원상세조회 selectOne
	public DogVO selectOneDog (String id){
		DogVO m = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectOneSQL);
				ps.setString(1, id);
			rs = ps.executeQuery();
			
			while(rs.next()){
				m = new DogVO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7),
						rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13));
				//m.setDogno(rs.getString(1));
				//m.setRegdate(rs.getString(12));
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		
		return m;
	}
	
	// 안락사 되지 않은 강아지
	public ArrayList<DogVO> mercykillingDog() {
		ArrayList<DogVO> lista = new ArrayList<>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(mercykillingSQL);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				DogVO m = new DogVO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), null, null, rs.getString(6), null, rs.getString(7), null, rs.getString(8), null);
				lista.add(m);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return lista;
	}
	
	// 일치하는 sno 찾기
	public String findSnoDog(String sno) {
		String result = "";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(findSnoSQL);
			ps.setString(1, sno);
			rs = ps.executeQuery();
			while (rs.next()) {
				result = rs.getString(1);
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return result;
		
	}
	
	// 일치하는 tno값 찾기
	public String findTnoDog(String tno) {
		String result = "";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(findTnoSQL);
			ps.setString(1, tno);
			rs = ps.executeQuery();
			while (rs.next()) {
				result = rs.getString(1);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return result;
		
	}
	
	// tno값에 해당하는 type명 찾기
	public String findShelternameDog(String sheltername) {
		String result = "";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(findShelternameSQL);
			ps.setString(1, sheltername);
			rs = ps.executeQuery();
			while (rs.next()) {
				result = rs.getString(1);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return result;
	}
	
	// sno값에 해당하는 shelterneme값 찾기
	public String findTypeDog(String type) {
		String result = "";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(findTypeSQL);
			ps.setString(1, type);
			rs = ps.executeQuery();
			while (rs.next()) {
				result = rs.getString(1);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return result;
	}
	
	
}












